﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using OrderManage.Model;
using OrderManage.Utility;

namespace OrderManage.DAL
{
    public class OrderDAL : DbHelperBase
    {
        public int GetTotal(OrderQcInfo qcmodel)
        {
            var strSql = new StringBuilder();
            switch (qcmodel.Role)
            {
                case "0":
                    strSql.Append(" SELECT count(*) AS Total FROM [dt_Data] AS A ");
                    strSql.Append(" INNER JOIN dt_User AS B ON A.UserName=B.UserName where A.UserName=@UserName ");
                    break;
                case "1":
                    strSql.Append(" SELECT count(*) AS Total FROM [dt_Data] AS A ");
                    strSql.Append(" INNER JOIN dt_User AS B ON A.UserName=B.UserName where 1=1 ");
                    break;
            }
            if (!string.IsNullOrEmpty(qcmodel.StartDateTime))
            {
                strSql.Append(" AND CONVERT(varchar(100), A.[InputTime], 23)>=@StartDateTime ");
            }
            if (!string.IsNullOrEmpty(qcmodel.EndDateTime))
            {
                strSql.Append(" AND CONVERT(varchar(100), A.[InputTime], 23)<=@EndDateTime ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Mobile))
            {
                strSql.Append(" AND A.Mobile like '%" + qcmodel.Mobile + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Status))
            {
                strSql.Append(" AND A.Status = @Status ");
            }
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.UserName))
                    {
                        Db.AddInParameter(cmd, "@UserName", qcmodel.UserName);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.StartDateTime))
                    {
                        var startDateTime = Convert.ToDateTime(qcmodel.StartDateTime);
                        Db.AddInParameter(cmd, "@StartDateTime", startDateTime);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.EndDateTime))
                    {
                        var endDateTime = Convert.ToDateTime(qcmodel.EndDateTime);
                        Db.AddInParameter(cmd, "@EndDateTime", endDateTime);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<OrderInfo> GetOrderList(OrderQcInfo qcmodel)
        {
            var list = new List<OrderInfo>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            switch (qcmodel.Role)
            {
                case "0":
                    strSql.Append(" SELECT row_number() over (order by A.InputTime desc) AS idx, A.[OrderId],A.[UserName],B.[FullName],A.[Mobile],A.[RechargeAmount],A.[Status],A.[ErrorCode],A.[ErrorText],A.[InputTime] FROM [dt_Data] AS A ");
                    strSql.Append(" INNER JOIN dt_User AS B ON A.UserName=B.UserName where A.UserName=@UserName ");
                    break;
                case "1":
                    strSql.Append(" SELECT row_number() over (order by A.InputTime desc) AS idx, A.[OrderId],A.[UserName],B.[FullName],A.[Mobile],A.[RechargeAmount],A.[Status],A.[ErrorCode],A.[ErrorText],A.[InputTime] FROM [dt_Data] AS A ");
                    strSql.Append(" INNER JOIN dt_User AS B ON A.UserName=B.UserName where 1=1 ");
                    break;
            }
            if (!string.IsNullOrEmpty(qcmodel.StartDateTime))
            {
                strSql.Append(" AND CONVERT(varchar(100), A.[InputTime], 23)>=@StartDateTime ");
            }
            if (!string.IsNullOrEmpty(qcmodel.EndDateTime))
            {
                strSql.Append(" AND CONVERT(varchar(100), A.[InputTime], 23)<=@EndDateTime ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Mobile))
            {
                strSql.Append(" AND A.Mobile like '%" + qcmodel.Mobile + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Status))
            {
                strSql.Append(" AND A.Status = @Status ");
            }
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.UserName))
                    {
                        Db.AddInParameter(cmd, "@UserName", qcmodel.UserName);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.StartDateTime))
                    {
                        var startDateTime = Convert.ToDateTime(qcmodel.StartDateTime);
                        Db.AddInParameter(cmd, "@StartDateTime", startDateTime);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.EndDateTime))
                    {
                        var endDateTime = Convert.ToDateTime(qcmodel.EndDateTime);
                        Db.AddInParameter(cmd, "@EndDateTime", endDateTime);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.Status))
                    {
                        Db.AddInParameter(cmd, "@Status", qcmodel.Status);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new OrderInfo();
                            if (reader["OrderId"] != DBNull.Value)
                                model.OrderId = int.Parse(reader["OrderId"].ToString());
                            model.UserName = reader["UserName"] == obj ? null : Convert.ToString(reader["UserName"]);
                            model.FullName = reader["FullName"] == obj ? null : Convert.ToString(reader["FullName"]);
                            model.Mobile = reader["Mobile"] == obj ? null : Convert.ToString(reader["Mobile"]);
                            if (reader["RechargeAmount"] != DBNull.Value)
                                model.RechargeAmount = Convert.ToInt32(reader["RechargeAmount"].ToString());
                            if (reader["InputTime"] != DBNull.Value)
                                model.InputTime = Convert.ToDateTime(reader["InputTime"].ToString());
                            if (reader["Status"] != DBNull.Value)
                                model.Status = int.Parse(reader["Status"].ToString());
                            if (reader["ErrorCode"] != DBNull.Value)
                                model.ErrorCode = int.Parse(reader["ErrorCode"].ToString());
                            model.StrErrorCode = model.ErrorCode == 0 ? "" : model.ErrorCode.ToString();
                            model.ErrorText = reader["ErrorText"] == obj ? null : Convert.ToString(reader["ErrorText"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }


        public bool AddOrder(OrderInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [dt_Data]([UserName],[Mobile],[RechargeAmount]) ");
            strSql.Append(" VALUES(@UserName,@Mobile,@RechargeAmount) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserName", model.UserName);
                    Db.AddInParameter(cmd, "@Mobile", model.Mobile);
                    Db.AddInParameter(cmd, "@RechargeAmount", model.RechargeAmount);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveOrder(int id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [dt_Data] WHERE OrderId=@OrderId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@OrderId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool ModifyErrorCodeAndText(int orderId, int status, int errorCode, string errorText)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [dt_Data] SET [Status]=@Status,[ErrorCode] = @ErrorCode,[ErrorText] = @ErrorText WHERE [OrderId] = @OrderId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Status", status);
                    Db.AddInParameter(cmd, "@ErrorCode", errorCode);
                    Db.AddInParameter(cmd, "@ErrorText", errorText);
                    Db.AddInParameter(cmd, "@OrderId", orderId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public List<OrderEntity> GetOrders(int status)
        {
            var list = new List<OrderEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT [OrderId],[UserName],[Mobile],[RechargeAmount],[Status],[ErrorCode],[ErrorText],[InputTime] FROM [dt_Data] where 1=1 ");
            if (status.ToString() == "0")
            {
                strSql.Append(" AND [Status]=0 ");
            }
            if (status.ToString() == "1")
            {
                strSql.Append(" AND [Status]=1 ");
            }
            if (status.ToString() == "2")
            {
                strSql.Append(" AND [Status]=2 ");
            }
            strSql.Append(" order by OrderId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {

                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new OrderEntity();
                            if (reader["OrderId"] != DBNull.Value)
                                model.OrderId = int.Parse(reader["OrderId"].ToString());
                            model.UserName = reader["UserName"] == obj ? null : Convert.ToString(reader["UserName"]);
                            model.Mobile = reader["Mobile"] == obj ? null : Convert.ToString(reader["Mobile"]);
                            if (reader["RechargeAmount"] != DBNull.Value)
                                model.RechargeAmount = Convert.ToInt32(reader["RechargeAmount"].ToString());
                            if (reader["InputTime"] != DBNull.Value)
                                model.InputTime = Convert.ToDateTime(reader["InputTime"].ToString());
                            if (reader["Status"] != DBNull.Value)
                                model.Status = int.Parse(reader["Status"].ToString());
                            if (reader["ErrorCode"] != DBNull.Value)
                                model.ErrorCode = Convert.ToInt32(reader["ErrorCode"].ToString());
                            model.ErrorText = reader["ErrorText"] == obj ? "" : Convert.ToString(reader["ErrorText"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public List<OrderEntity> GetOrder(int status, int count)
        {
            var list = new List<OrderEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(string.Format("select top {0} [OrderId],[UserName],[Mobile],[RechargeAmount],[Status],[ErrorCode],[ErrorText],[InputTime] from dt_data WHERE Status={1} order by OrderId ", count, status));
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new OrderEntity();
                            if (reader["OrderId"] != DBNull.Value)
                                model.OrderId = int.Parse(reader["OrderId"].ToString());
                            model.UserName = reader["UserName"] == obj ? null : Convert.ToString(reader["UserName"]);
                            model.Mobile = reader["Mobile"] == obj ? null : Convert.ToString(reader["Mobile"]);
                            if (reader["RechargeAmount"] != DBNull.Value)
                                model.RechargeAmount = Convert.ToInt32(reader["RechargeAmount"].ToString());
                            if (reader["InputTime"] != DBNull.Value)
                                model.InputTime = Convert.ToDateTime(reader["InputTime"].ToString());
                            if (reader["Status"] != DBNull.Value)
                                model.Status = int.Parse(reader["Status"].ToString());
                            if (reader["ErrorCode"] != DBNull.Value)
                                model.ErrorCode = Convert.ToInt32(reader["ErrorCode"].ToString());
                            model.ErrorText = reader["ErrorText"] == obj ? "" : Convert.ToString(reader["ErrorText"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public int GetOrderCount(int status)
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(*) AS Total FROM [dt_Data] WHERE Status=@Status ");
            var result = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Status", status);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            result = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return result;
            }
        }


        public List<OrderInfo> GetTotalAmount(OrderQcInfo qcmodel)
        {
            var list = new List<OrderInfo>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            switch (qcmodel.Role)
            {
                case "0":
                    strSql.Append(" SELECT  A.[RechargeAmount],A.[Status] FROM [dt_Data] AS A ");
                    strSql.Append(" INNER JOIN dt_User AS B ON A.UserName=B.UserName where A.UserName=@UserName ");
                    break;
                case "1":
                    strSql.Append(" SELECT A.[RechargeAmount],A.[Status] FROM [dt_Data] AS A ");
                    strSql.Append(" INNER JOIN dt_User AS B ON A.UserName=B.UserName where 1=1 ");
                    break;
            }
            if (!string.IsNullOrEmpty(qcmodel.StartDateTime))
            {
                strSql.Append(" AND CONVERT(varchar(100), A.[InputTime], 23)>=@StartDateTime ");
            }
            if (!string.IsNullOrEmpty(qcmodel.EndDateTime))
            {
                strSql.Append(" AND CONVERT(varchar(100), A.[InputTime], 23)<=@EndDateTime ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Mobile))
            {
                strSql.Append(" AND A.Mobile like '%" + qcmodel.Mobile + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.Status))
            {
                strSql.Append(" AND A.Status = @Status ");
            }

            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.UserName))
                    {
                        Db.AddInParameter(cmd, "@UserName", qcmodel.UserName);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.StartDateTime))
                    {
                        var startDateTime = Convert.ToDateTime(qcmodel.StartDateTime);
                        Db.AddInParameter(cmd, "@StartDateTime", startDateTime);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.EndDateTime))
                    {
                        var endDateTime = Convert.ToDateTime(qcmodel.EndDateTime);
                        Db.AddInParameter(cmd, "@EndDateTime", endDateTime);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.Status))
                    {
                        Db.AddInParameter(cmd, "@Status", qcmodel.Status);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new OrderInfo();
                            if (reader["RechargeAmount"] != DBNull.Value)
                                model.RechargeAmount = Convert.ToInt32(reader["RechargeAmount"].ToString());
                            if (reader["Status"] != DBNull.Value)
                                model.Status = int.Parse(reader["Status"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public IList<ChartInfo> GetTotalRechargeAmountChartList(string tyear)
        {
            var list = new List<ChartInfo>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT SUM(RechargeAmount) AS TotalRechargeAmount,datepart(year,InputTime) AS [Year],datepart(month,InputTime) AS [Month] FROM dt_Data where Status=1 ");
            if (!string.IsNullOrEmpty(tyear))
            {
                strSql.Append(" and DATEPART(year, InputTime) =@TYear ");
            }
            strSql.Append(" group by datepart(year,InputTime),datepart(month,InputTime) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(tyear))
                    {
                        Db.AddInParameter(cmd, "@TYear", tyear);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new ChartInfo();
                            var year = reader["Year"] == obj ? null : Convert.ToString(reader["Year"]);
                            var month = reader["Month"] == obj ? null : Convert.ToString(reader["Month"]);
                            model.Date = year + "-" + month;
                            if (reader["TotalRechargeAmount"] != DBNull.Value)
                                model.TotalRechargeAmount = Convert.ToInt32(reader["TotalRechargeAmount"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
